Namespace SiteMap
    ''' <summary>
    ''' Proveedor de Site Maps para crealos con la estructura definida en Bitworks
    ''' </summary>
    ''' <remarks></remarks>
    Public Class dbSiteMapProvider
        Inherits System.Web.StaticSiteMapProvider

        Dim _rootNode As System.Web.SiteMapNode
        Dim _cnn As Data.IDbConnection
        Dim _nombreTabla As String

        Public Overrides Sub Initialize(ByVal name As String, ByVal attributes As System.Collections.Specialized.NameValueCollection)
            MyBase.Initialize(name, attributes)
            _nombreTabla = attributes("nombreTabla")
            Dim cnnSettings As System.Configuration.ConnectionStringSettings = System.Configuration.ConfigurationManager.ConnectionStrings(attributes("nombreConexion"))

            If cnnSettings.ProviderName = "System.Data.SqlClient" Then
                _cnn = New SqlClient.SqlConnection(cnnSettings.ConnectionString)
            ElseIf cnnSettings.ProviderName = "System.OracleClient.OracleConnection" Then
                _cnn = New OracleClient.OracleConnection(cnnSettings.ConnectionString)
            ElseIf cnnSettings.ProviderName = "System.OleDb.OleDbConnection" Then
                _cnn = New OleDb.OleDbConnection(cnnSettings.ConnectionString)
            End If

        End Sub

        Public Overrides Function BuildSiteMap() As System.Web.SiteMapNode
            Dim cont As System.Web.HttpContext = System.Web.HttpContext.Current
            _rootNode = CType(cont.Cache("nodoRaiz"), Web.SiteMapNode)
            If _rootNode Is Nothing Then
                SyncLock Me
                    Dim cerrar As Boolean = False
                    If _cnn.State = ConnectionState.Closed Then
                        _cnn.Open()
                        cerrar = True
                    End If
                    'falta busqueda en Cache
                    armaSiteMap()
                    If cerrar Then
                        _cnn.Close()
                        _cnn.Dispose()
                        _cnn = Nothing
                    End If
           
                End SyncLock
            End If
            Return _rootNode
        End Function
        Private Sub armaSiteMap()
            Clear()
            _rootNode = getRaiz()
            AddNode(_rootNode)
            llenarHijos(_rootNode)
            Dim cont As System.Web.HttpContext = System.Web.HttpContext.Current
            cont.Cache.Insert("nodoRaiz", _rootNode)
        End Sub
        Private Sub llenarHijos(ByVal padre As System.Web.SiteMapNode)
            Dim sql As String = "select codigo, titulo, tituloPagina, Descripcion, metaKeyWords, metaDescription, Url, urlAmigable, padre, fechaInicioPub, fechaFinPub,  seccion from " & _nombreTabla & " where padre=@padre"
            Dim cmd As Data.IDbCommand
            Dim da As Data.IDataAdapter
            Dim dt As New Data.DataTable
            If TypeOf _cnn Is SqlClient.SqlConnection Then
                cmd = New SqlClient.SqlCommand(Sql, _cnn)
                With CType(cmd, SqlClient.SqlCommand)
                    .Parameters.Add("@padre", SqlDbType.Int).Value = CInt(padre.Key)
                End With
                da = New SqlClient.SqlDataAdapter(cmd)
                CType(da, SqlClient.SqlDataAdapter).Fill(dt)

            ElseIf TypeOf _cnn Is Data.OracleClient.OracleConnection Then
                cmd = New OracleClient.OracleCommand(sql, _cnn)
                With CType(cmd, OracleClient.OracleCommand)
                    .Parameters.Add(":padre", OracleClient.OracleType.Int32).Value = CInt(padre.Key)
                End With
                da = New OracleClient.OracleDataAdapter(cmd)
                CType(da, Data.OracleClient.OracleDataAdapter).Fill(dt)
            ElseIf TypeOf _cnn Is Data.OleDb.OleDbConnection Then
                cmd = New OleDb.OleDbCommand(sql, _cnn)
                With CType(cmd, OleDb.OleDbCommand)
                    .Parameters.Add("@padre", OleDb.OleDbType.Integer).Value = CInt(padre.Key)
                End With
                da = New OleDb.OleDbDataAdapter(cmd)
                CType(da, Data.OleDb.OleDbDataAdapter).Fill(dt)
            End If
            Dim sm As New clSiteMap
            Dim auxNode As System.Web.SiteMapNode
            Dim dr As Data.DataRow
            For Each dr In dt.Rows
                sm = New clSiteMap()
                With sm
                    .codigo = dr.Item(0)
                    .codigo = dr.Item(0)
                    .Titulo = dr.Item(1)
                    .TituloPagina = dr.Item(2)
                    .Descripcion = dr.Item(3)
                    If dr(4) Is System.DBNull.Value = False Then .MetaKeyWords = dr.Item(4)
                    If dr(5) Is System.DBNull.Value = False Then .MetaDescription = dr.Item(5)
                    If dr(6) Is System.DBNull.Value = False Then .Url = dr.Item(6)
                    If dr(7) Is System.DBNull.Value = False Then .UrlAmigable = dr.Item(7)
                    If dr(8) Is System.DBNull.Value = False Then .padre = dr.Item(8)
                End With
                auxNode = getNodeDeSiteMap(sm)
                AddNode(auxNode, padre)
                llenarHijos(auxNode)
            Next
        End Sub
        Private Function getRaiz() As System.Web.SiteMapNode
            Dim cmd As Data.IDbCommand
            If TypeOf _cnn Is SqlClient.SqlConnection Then
                cmd = New SqlClient.SqlCommand("select codigo from " & _nombreTabla & " where padre is null", _cnn)
            ElseIf TypeOf _cnn Is Data.OracleClient.OracleConnection Then
                cmd = New OracleClient.OracleCommand("select codigo from " & _nombreTabla & " where padre is null", _cnn)
            ElseIf TypeOf _cnn Is Data.OleDb.OleDbConnection Then
                cmd = New OleDb.OleDbCommand("select codigo from " & _nombreTabla & " where padre is null", _cnn)
            End If
            Dim lector As Data.IDataReader = cmd.ExecuteReader
            Dim codigo As Integer = 0
            If lector.Read Then
                codigo = lector(0)
            End If
            lector.Close()
            Dim sm As New clSiteMap(codigo)
            Return getNodeDeSiteMap(sm)
        End Function
        Private Function getNodeDeSiteMap(ByVal sm As clSiteMap) As System.Web.SiteMapNode
            Dim sNode As New System.Web.SiteMapNode(Me, sm.codigo, sm.Url, sm.Titulo, sm.Descripcion)
            With sm
                sNode.Item("tituloPagina") = .TituloPagina
                sNode.Item("metaKeyWords") = .MetaKeyWords
                sNode.Item("metaKeyDescription") = .MetaDescription
                sNode.Item("urlAmigable") = .UrlAmigable
            End With
            Return sNode
        End Function
        Protected Overrides Function GetRootNodeCore() As System.Web.SiteMapNode
            Return BuildSiteMap()
        End Function
    End Class
End Namespace

